Create Table

Creating a New Table

When creating a new table in the database, you can define various columns with different properties, such as data types, constraints, and default values. Below are a few examples showcasing how to use the table creation UI.

Table Name and Description

• Table Name: The name of the table is entered at the top of the form (e.g., admin).
• Description: You can provide a description of the table, explaining its purpose.

Example 1: Creating a Basic Table

Here’s an example of a simple table definition for a users table:

• Table Name: users
• Description: Stores basic information of users.

Column Name	Data Type	Properties	Default Value
id	int	Primary Key, Auto Increment, Not Null
username	varchar(50)	Not Null
email	varchar(100)	Unique, Not Null
password_hash	varchar(255)	Not Null
created_at	timestamp	Not Null	CURRENT_TIMESTAMP

• + Add Column: Add additional columns for more user information.
• + Add Index: You can add an index on the email column to optimize searches for user email addresses.

Properties

• Primary Key: The id column is marked as the primary key.
• Auto Increment: The id is auto-incremented for each new row.
• Not Null: Ensures that all important fields (e.g., username, email, and password_hash) cannot be empty.

Example 2: Creating an Orders Table

Here’s an example of a table that stores orders information:

• Table Name: orders
• Description: Stores order information for the e-commerce platform.

Column Name	Data Type	Properties	Default Value
order_id	int	Primary Key, Auto Increment, Not Null
user_id	int	Foreign Key (references users.id), Not Null
total_amount	decimal(10,2)	Not Null
order_status	varchar(20)	Not Null	pending
order_date	timestamp	Not Null	CURRENT_TIMESTAMP
shipping_date	timestamp	Null

Properties

• Primary Key: The order_id column is marked as the primary key.
• Foreign Key: The user_id column references the id column in the users table.
• Not Null: Key fields like user_id, total_amount, order_status, and order_date are mandatory.
• Default Value: The order_status is set to pending by default when a new order is created.

Adding an Index

• Index on user_id: You can add an index on user_id to optimize the retrieval of orders for a particular user.
• Composite Index: A composite index on user_id and order_date can be added to optimize searching for orders by a specific user within a certain date range.

Example 3: Creating a Products Table with Different Data Types

Here’s an example of a table that stores product information:

• Table Name: products
• Description: Stores product details for an e-commerce platform.

Column Name	Data Type	Properties	Default Value
product_id	int	Primary Key, Auto Increment, Not Null
product_name	varchar(100)	Not Null
description	text	Null
price	decimal(10,2)	Not Null
stock_quantity	int	Not Null	0
category_id	int	Foreign Key (references categories.category_id)

Properties

• Primary Key: The product_id column is marked as the primary key.
• Foreign Key: The category_id column references the category_id in the categories table.
• Not Null: Ensures that product_name, price, and stock_quantity fields must have values.
• Default Value: The stock_quantity defaults to 0.

Example 4: Creating a Categories Table with Nested Indexes

Here’s an example of a table that stores product categories with index management:

• Table Name: categories
• Description: Stores category information for products.

Column Name	Data Type	Properties	Default Value
category_id	int	Primary Key, Auto Increment, Not Null
category_name	varchar(100)	Not Null
parent_id	int	Foreign Key (self-reference), Null

Properties

• Primary Key: The category_id column is marked as the primary key.
• Foreign Key: The parent_id column is a self-referencing foreign key, allowing a category to have a parent category.
• Not Null: The category_name field must have a value.

Adding Indexes

• Composite Index: You can add a composite index on category_id and parent_id to improve the performance of hierarchical category queries.

---

Summary

The table creation process in this UI allows users to define columns, data types, properties, and default values with ease. Indexes can be added to columns to improve query performance, and relationships can be defined between tables using foreign keys. The intuitive interface simplifies the creation and management of database tables.